package com.md.common.utils.excel;

import com.md.common.exception.BaseBizException;
import com.md.common.utils.excel.model.ExcelCell;
import com.md.common.utils.excel.model.ExcelCellData;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.io.*;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;

@Component("poiExcelHandler")
public class POIExcelHandler extends AbstractExcelHandler {

	private static final String XLS = ".xls";
	private static final String XLSX = ".xlsx";

// private static final DateFormat SHORT_DATE_DF = new SimpleDateFormat("yyyy-MM-dd");
//    private static final DateFormat LONG_DATE_DF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//    private static final DateFormat TIME_DF = new SimpleDateFormat("H:m:s");
    private static final DataFormatter DATE_FORMATTER = new DataFormatter();

    private static final NumberFormat NUMBER_FORMAT = new DecimalFormat("#.##########");

	private static final Logger LOGGER = LoggerFactory.getLogger(POIExcelHandler.class);

	static {
		NUMBER_FORMAT.setGroupingUsed(false);
	}

	@Override
	public List<List<ExcelCellData>> readExcel(File excel, int ignoreRow, int sheetNo, int colCount) {

		if (excel == null || !excel.exists())
			throw new BaseBizException(String.format("文件“%s”不存在", excel));

		// 获得Workbook工作薄对象
		Workbook workbook = null;

		try {
			workbook = getWorkBook(excel);

			// 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
			List<List<ExcelCellData>> datas = new ArrayList<>();
			// 获得当前sheet工作表
			Sheet sheet = workbook.getSheetAt(sheetNo);

			FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();


			for (int i = ignoreRow, rowCount = sheet.getLastRowNum(); i <= rowCount; i++) {
				// 获得当前行
				Row row = sheet.getRow(i);
				if (row == null)
					continue;

				// 获得当前行的开始列
				int firstCellNum = row.getFirstCellNum();
				// 获得当前行的列数
				int lastCellNum = colCount == -1 ? row.getLastCellNum() : colCount;
				List<ExcelCellData> cells = new ArrayList<>();
				// 循环当前行
				for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
					Cell cell = row.getCell(cellNum);
					cells.add(getCellValue(cell, formulaEvaluator));
				}
				datas.add(cells);
			}
			workbook.close();
			return datas;
		} catch (Exception e) {
			LOGGER.error(e.getMessage(), e);
			throw new BaseBizException("文件读取失败");
		} finally {
			if (workbook != null)
				try {
					workbook.close();
				} catch (IOException ignored) {
				}
		}
	}

	@Override
	public File writeExcel(String filePath, List<String> titles, List<List<String>> datas, String sheetName, int sheetNo) {
		return writeExcel(filePath, null, titles, datas, sheetName, sheetNo);
	}

	@Override
	public File writeExcel(String filePath, List<ExcelCell> mainTitles, List<String> titles, List<List<String>> datas, String sheetName,
						   int sheetNo) {
		File file = new File(filePath);
		if (!file.getParentFile().exists())
			file.getParentFile().mkdirs();
		Workbook workbook = null;
		try (FileOutputStream fos = new FileOutputStream(file)) {
			if (filePath.endsWith(XLS)) {
				workbook = new HSSFWorkbook();
			} else if (filePath.endsWith(XLSX)) {
				workbook = new XSSFWorkbook();
			} else {
				throw new BaseBizException("非法文件，只支持“.xls”，“.xlsx”文件");
			}

			Sheet sheet = workbook.createSheet(sheetName);

			int rn = 0;

			if (CollectionUtils.isNotEmpty(mainTitles)) {

				CellStyle mainTitleCellStyle = workbook.createCellStyle();
				mainTitleCellStyle.setAlignment(HorizontalAlignment.CENTER);
				mainTitleCellStyle.setBorderTop(BorderStyle.THIN);
				mainTitleCellStyle.setBorderRight(BorderStyle.THIN);
				mainTitleCellStyle.setBorderBottom(BorderStyle.THIN);
				mainTitleCellStyle.setBorderLeft(BorderStyle.THIN);
				mainTitleCellStyle.setTopBorderColor((short) 0);
				mainTitleCellStyle.setRightBorderColor((short) 0);
				mainTitleCellStyle.setBottomBorderColor((short) 0);
				mainTitleCellStyle.setLeftBorderColor((short) 0);
				mainTitleCellStyle.setWrapText(true);
				Font mainTitleFont = workbook.createFont();
				mainTitleFont.setBold(true);
				mainTitleCellStyle.setFont(mainTitleFont);

				Row row = sheet.createRow(0);
				rn += mainTitles.stream().mapToInt(ExcelCell::getLastRow).max().orElse(0) + 1;
				for (ExcelCell mgrCell : mainTitles) {
					CellRangeAddress region = new CellRangeAddress(mgrCell.getFirstRow(),
							mgrCell.getLastRow(), mgrCell.getFirstCol(), mgrCell.getLastCol());
					sheet.addMergedRegion(region);
					Cell cell = row.createCell(mgrCell.getFirstCol());
					cell.setCellValue(mgrCell.getContent());
					cell.setCellStyle(mainTitleCellStyle);
				}

			}

			if (CollectionUtils.isNotEmpty(titles)) {
				CellStyle titleCellStyle = workbook.createCellStyle();
				titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
				titleCellStyle.setBorderTop(BorderStyle.THIN);
				titleCellStyle.setBorderRight(BorderStyle.THIN);
				titleCellStyle.setBorderBottom(BorderStyle.THIN);
				titleCellStyle.setBorderLeft(BorderStyle.THIN);
				titleCellStyle.setWrapText(true);
				Font titleFont = workbook.createFont();
				titleFont.setBold(true);
				titleCellStyle.setFont(titleFont);
				Row row = sheet.createRow(rn);
				for (int j = 0; j < titles.size(); j++) {
					Cell cell = row.createCell(j);
					cell.setCellValue(titles.get(j));
					cell.setCellStyle(titleCellStyle);
				}
				rn += 1;
			}

			for (int i = 0, rowCount = datas.size(); i < rowCount; i++) {
				List<String> rowDatas = datas.get(i);
				Row row = sheet.createRow(i + rn);
				for (int j = 0, colCount = rowDatas.size(); j < colCount; j++) {
					Cell cell = row.createCell(j);
					cell.setCellValue(rowDatas.get(j));
				}
			}

			workbook.write(fos);
		} catch (Exception e) {
			LOGGER.error(e.getMessage(), e);
			throw new BaseBizException("文件写入失败");
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (IOException ignored) {
				}
			}
		}
		return file;
	}

	public static void setRegionStyle(Sheet sheet, CellRangeAddress region, CellStyle style) {
		for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
			Row row = sheet.getRow(i);
			if (row == null) continue;
			for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
				Cell cell = row.getCell(j);
				if (cell == null) continue;
					cell.setCellStyle(style);
			}
		}
	}

	private static Workbook getWorkBook(File file) throws IOException {
		// 获得文件名
		String fileName = file.getName();
		// 创建Workbook工作薄对象，表示整个excel
		// 获取excel文件的io流
		try (InputStream is = new FileInputStream(file)) {
			// 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
			if (fileName.endsWith(XLS)) {
				return new HSSFWorkbook(is);
			} else if (fileName.endsWith(XLSX)) {
				return new XSSFWorkbook(is);
			} else {
				throw new BaseBizException("非法文件，只支持“.xls”，“.xlsx”文件");
			}
		}
	}

	private static ExcelCellData getCellValue(Cell cell, FormulaEvaluator formulaEvaluator ) {
		ExcelCellData data = new ExcelCellData();
		if (cell == null) {
			data.setType(ExcelCellData.Type.String);
			data.setValue("");
			return data;
		}

		CellType cellType = cell.getCellTypeEnum();

		switch (cellType) {
		case BOOLEAN:
			data.setType(ExcelCellData.Type.String);
			data.setValue(String.valueOf(cell.getBooleanCellValue()));
			break;
		case FORMULA:
			data.setType(ExcelCellData.Type.String);
			data.setValue(String.valueOf(cell.getCellFormula()));
			CellValue value = formulaEvaluator.evaluate(cell);
			readCellValue(data, value, formulaEvaluator);
			break;
		case NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				data.setType(ExcelCellData.Type.Date);
				data.setValue(DATE_FORMATTER.formatCellValue(cell));
			} else {
				data.setType(ExcelCellData.Type.Number);
//				data.setValue(String.valueOf(cell.getNumericCellValue()));
				// 出现四舍五入问题 20181029 Ice
				data.setValue(NUMBER_FORMAT.format(cell.getNumericCellValue()));
			}
			break;
		case STRING:
			data.setType(ExcelCellData.Type.String);
			data.setValue(String.valueOf(cell.getStringCellValue()));
			break;
		default:
			data.setType(ExcelCellData.Type.String);
			data.setValue("");
			break;
		}

		return data;
	}

	private static void readCellValue(ExcelCellData data, CellValue cellValue, FormulaEvaluator formulaEvaluator ) {

		CellType cellType = cellValue.getCellTypeEnum();

		switch (cellType) {
			case BOOLEAN:
				data.setType(ExcelCellData.Type.String);
				data.setValue(String.valueOf(cellValue.getBooleanValue()));
				break;
			case NUMERIC:
				data.setType(ExcelCellData.Type.Number);
				data.setValue(NUMBER_FORMAT.format(cellValue.getNumberValue()));
				break;
			case STRING:
				data.setType(ExcelCellData.Type.String);
				data.setValue(String.valueOf(cellValue.getStringValue()));
				break;
			default:
				data.setType(ExcelCellData.Type.String);
				data.setValue("");
				break;
		}
	}

}
